const mysql = require('mysql2');

class MysqlDB {
    res={code:200,data:[],message:''}
    constructor() {
    }

    initConnect(connect) {
        connect=this.convertToJSON(connect)
        let  pool = mysql.createPool({
            host: connect.mainData.host,
            port: connect.mainData.port,
            user: connect.mainData.username,
            password: connect.mainData.password,
            database: connect.mainData.database,
            waitForConnections: true,
            connectionLimit: 2,
            maxIdle: 2, // 最大空闲连接数，默认等于 `connectionLimit`
            idleTimeout: 60000, // 空闲连接超时，以毫秒为单位，默认值为 60000 ms
            queueLimit: 0,
            enableKeepAlive: true,
            keepAliveInitialDelay: 0,
        });
        return pool;
    }

    connectDatabase(connect) {
        connect=this.convertToJSON(connect)
        let pool = this.initConnect(connect);
        return pool
    }

    async   pingDatabase(connect) {
        connect=this.convertToJSON(connect)
        let localConnect = mysql.createConnection({
            host: connect.mainData.host,
            port: connect.mainData.port,
            user: connect.mainData.username,
            password: connect.mainData.password,
            database: connect.mainData.database
        });
        try {
            // 使用 await 等待查询结果
            this.res.data=await this.pingDataBaseConnect(localConnect);
            this.res.message='连接成功'
            // 在这里你可以安全地使用 myVariable，因为它已经包含了查询结果
        } catch (error) {
            this.res.code=500
            this.res.message=`(${error.errno})`+error.sqlMessage
        } finally {
            // 确保关闭数据库连接
            localConnect.end();
        }
        return this.res
    }

     pingDataBaseConnect(pool){
        return new Promise((resolve, reject) => {
            pool.query("show databases;", (error, results, fields) => {
                if (error) reject(error);
                resolve(results);
            });
        });
    }


    async executeQuery(connect, script) {
        // 连接到数据库
        connect=this.convertToJSON(connect)
        let pool = this.connectDatabase(connect)
        if(pool==null){
            this.res.code=500
            this.res.message='操作失败'
            return this.res
        }
        try {
            // 使用 await 等待查询结果
            this.res.data=await this.queryDataBase(pool,script);
            // 在这里你可以安全地使用 myVariable，因为它已经包含了查询结果
        } catch (error) {
            this.res.code=500
            this.res.message=`(${error.errno})`+error.sqlMessage
            console.error(error);
        }finally {
            pool.end()
        }
        return this.res
    }

    queryDataBase(pool,script){
        console.log('execute sql=====>',script);
        return new Promise((resolve, reject) => {
            pool.query(script, (error, results, fields) => {
                const resultObject={}
                resultObject.fieldList=this.getFieldList(fields)
                resultObject.dataList=results?results:[]
                if(!results){
                    console.log('execute results=====>',results);
                }else {
                    console.log('execute results length=====>',results.length);
                }
                console.log('execute error=====>',error);
                if (error) reject(error);
                resolve(resultObject);
            });
        });
    }

    convertToJSON(item) {
        if (typeof item === 'string') {
            return JSON.parse(item)
        }
        return item
    }

    getFieldList(fields) {
        if(fields){
            let resFields=[]
            fields.forEach(field=>{
                let row={}
                row.COLUMN_NAME=field.name;
                // 获取字段类型名称
                const type = field.type;
                const length = field.length;
                const flags = field.flags;
                let typeName;
                switch (type) {
                    case 0: typeName = 'decimal'; break;
                    case 1: typeName = 'tiny'; break;
                    case 2: typeName = 'SHORT'; break;
                    case 3: typeName = 'int'; break; // 更正这里
                    case 4: typeName = 'float'; break;
                    case 5: typeName = 'double'; break;
                    case 6: typeName = 'null'; break;
                    case 7: typeName = 'timestamp'; break;
                    case 8: typeName = 'bigint'; break;
                    case 9: typeName = 'int24'; break;
                    case 10: typeName = 'date'; break;
                    case 11: typeName = 'time'; break;
                    case 12: typeName = 'datetime'; break;
                    case 13: typeName = 'year'; break;
                    case 14: typeName = 'date'; break;
                    case 15: typeName = 'varchar'; break; // MySQL 5.0.x
                    case 16: typeName = 'bit'; break;
                    case 246: typeName = 'decimal'; break; // 新的 DECIMAL 类型
                    case 247: typeName = 'enum'; break;
                    case 248: typeName = 'set'; break;
                    case 249: typeName = 'tiny_blob'; break;
                    case 250: typeName = 'medium_blob'; break;
                    case 251: typeName = 'long_blob'; break;
                    case 252: typeName = 'blob'; break;
                    case 253: typeName = 'varchar'; break; // MySQL 5.0.3 及以后版本
                    case 254: typeName = 'char'; break; // For a fixed-length string
                    case 255: typeName = 'geometry'; break;
                    default: typeName = 'unknown'; break;
                }

                // 使用 flags 和 length 来细化类型
                if (typeName === 'tiny') {
                    if (flags & 128) { // TINYINT(1)
                        typeName = 'boolean';
                    }
                } else if (typeName === 'int') {
                    if (length === 1) {
                        typeName = 'tinyint';
                    } else if (length === 2) {
                        typeName = 'smallint';
                    } else if (length === 3) {
                        typeName = 'mediumint';
                    }
                }
                row.DATA_TYPE=typeName;
                resFields.push(row);
            })
            return resFields
        }
        return [];
    }
}

MysqlDB.toString = () => '[class MysqlDB]';
module.exports = MysqlDB
